package paper.db;

import java.sql.*;

import java.util.Date;
import java.util.Random;
import java.util.Enumeration;

import java.net.NetworkInterface;
import java.net.InetAddress;

import java.text.SimpleDateFormat;

public class DB {
    private final static SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss");
    public static Connection connection;

    public final static String driverName = "org.postgresql.Driver";
    public final static String connectionUsername = "master";
    public final static String connectionPassword = "nigel=HOD;";
    public static String connectionURL;
    static {
        try {
            String localhost = "localhost"; // default, home machine.
            // Hack to work out which db to connect to...
            for (Enumeration<NetworkInterface> i = NetworkInterface.getNetworkInterfaces();
                 i.hasMoreElements();) {
                NetworkInterface ni = i.nextElement();
                for (Enumeration<InetAddress> j = ni.getInetAddresses();
                     j.hasMoreElements();) {
                    byte b[] = j.nextElement().getAddress();
                    if ((b[0] & 0xff) == 137 && (b[1] & 0xff) == 222) {
                        localhost = "mordac";  // uni network.
                    }
                }
            }
            connectionURL = "jdbc:postgresql://" + localhost + "/bib";
            System.out.println("Connecting to " + connectionURL);
            Class.forName(driverName);
            connection = DriverManager.getConnection(connectionURL,
                                                     connectionUsername,
                                                     connectionPassword);



        } catch(Exception e) {
            System.err.println("Woopsiedesie " + e);
        }
    }

    // papers

    public static final String key          = "key";           public static final int keyMax = 25;
    public static final String date         = "date";          public static final int dateMax = 25;
    public static final String reason       = "reason";        public static final int reasonMax = 25;
    public static final String revision     = "revision";
    public static final String title        = "title";         public static final int titleMax = 256;
    public static final String authors      = "authors";       public static final int authorsMax = 256;
    public static final String email        = "email";         public static final int emailMax = 256;
    public static final String abstractX    = "abstract";      public static final int abstractXMax = 2048;
    public static final String address      = "address";       public static final int addressMax = 256;
    public static final String booktitle    = "booktitle";     public static final int booktitleMax = 256;
    public static final String editor       = "editor";        public static final int editorMax = 256;
    public static final String howpublished = "howpublished";  public static final int howpublishedMax = 256;
    public static final String institution  = "institution";   public static final int institutionMax = 256;
    public static final String isbn         = "isbn";          public static final int isbnMax = 25;
    public static final String issn         = "issn";          public static final int issnMax = 15;
    public static final String issue        = "issue";         public static final int issueMax = 15;
    public static final String journal      = "journal";       public static final int journalMax = 256;
    public static final String month        = "month";         public static final int monthMax = 15;
    public static final String number       = "number";        public static final int numberMax = 15;
    public static final String pages        = "pages";         public static final int pagesMax = 15;
    public static final String publisher    = "publisher";     public static final int publisherMax = 256;
    public static final String school       = "school";        public static final int schoolMax = 256;
    public static final String volume       = "volume";        public static final int volumeMax = 15;
    public static final String year         = "year";          public static final int yearMax = 15;
    public static final String bibTexKey    = "bibtexkey";     public static final int bibTexKeyMax = 30;
    public static final String pdf          = "pdf";           public static final int pdfMax = 256;
    public static final String url          = "url";           public static final int urlMax = 256;   //fixme, should be 512
    public static final String active       = "active";

    // citations

    public static final String paper        = "paper";         public static final int paperMax = 16;
    public static final String cites        = "cites";         public static final int citesMax = 16;
    public static final String outOf        = "outof";
    public static final String probability  = "probability";
    // authors from paper
    // title from paper
    // howpublished from paper
    // active from paper

    // flattitle

    public static final String flattitle    = "flattitle";     public static final int flattitleMax = 256;
    public static final String paperKey     = "paperkey";      public static final int paperKeyMax = 16;
    // active from paper

    // pdffile

    public static final String md5          = "md5";           public static final int md5Max = 32;
    public static final String analysed       = "analysed";        public static final int analysedMax = 1; // '1', '0' (failed)
    public static final String copyrighted  = "copyrighted";   public static final int copyrightedMax = 1; // '1', '0'
    public static final String data         = "data"; // raw data
    // paperKey from index
    // date from papers
    // authors from paper
    // title from paper
    // howpublished from paper
    // email from paper
    // active from paper



    // email addresses
    
    // email from paper
    public static final String userID       = "userid";        public static final int userIDMax = 16; 

    // users

    // userID from email
    public static final String password     = "password";      public static final int passwordMax = 32;
    public static final String cookie       = "cookie";        public static final int cookieMax = 32;
    public static final String resetPassword= "resetpassword";   public static final int resetPasswordMax = 32;
    public static final String surname      = "surname";       public static final int surnameMax = 32; 
    public static final String givenName    = "givenName";     public static final int givenNameMax = 32; 
    public static final String affiliation  = "affiliation";   public static final int affiliationMax = 32; 


    // userpapers

    // userID from email
    // paperKey from index
    // md5 from pdffiles
    public static final String personalNotes  = "personalnotes";
    public static final String sequence  = "sequence";

	
    

    // filequeue

    public static final String type          = "type";         public static final int typeMax = 4; // 'key', 'url', 'file'
    // userID from email
    public static final String originalFile  = "originalfile";  public static final int originalFileMax = 128;
    // sequence from userpapers                       
    // md5 from pdffile
    public static final String fileName      = "filename";      public static final int fileNameMax = 256;
    public static final String fileSize      = "filesize";
    // copyrighted from pdffiles
    // url from paper
    public static final String hopCount      = "hopcount";
    // paperkey from flattitle

    // authors

    // author from paper
    // paperkey from flattitle
    // active from paper
    // sequence from userpapers

    // urls

    // url from ...
    // date from ...
    public static final String status        = "status";           public static final int statusMax = 1;
    public static final String urlStatusQueued  = "Q";
    public static final String urlStatusDone    = "+";
    public static final String urlStatusHorizon = "H";


	// tags stuff
    public static final String maxTagLength= "512";
    public static final String tag= "tag";
    public static final String tagid= "tagid";
    public static final String isinvisible= "isinvisible";
    public static final String tagorigin= "origin";
    public static final String isprivate= "isprivate";
	
	
    public static final String createTablePapers = "CREATE TABLE PAPERS (" +
	    key          + " varchar(" + keyMax  + ") not null unique primary key, " + 
	    date         + " varchar(" + dateMax  + ") not null, " + 
	    reason       + " varchar(" + reasonMax  + ") not null, " + 
	    revision     + " int, " + 
	    title        + " varchar(" + titleMax  + ") not null, " + 
	    authors      + " varchar(" + authorsMax  + ") not null, " + 
	    email        + " varchar(" + emailMax  + ") not null, " + 
	    abstractX    + " varchar(" + abstractXMax  + ") not null, " + 
	    address      + " varchar(" + addressMax  + ") not null, " + 
	    booktitle    + " varchar(" + booktitleMax  + ") not null, " + 
	    editor       + " varchar(" + editorMax  + ") not null, " + 
	    howpublished + " varchar(" + howpublishedMax  + ") not null, " + 
	    institution  + " varchar(" + institutionMax  + ") not null, " + 
	    isbn         + " varchar(" + isbnMax  + ") not null, " + 
	    issn         + " varchar(" + issnMax  + ") not null, " + 
	    issue        + " varchar(" + issueMax  + ") not null, " + 
	    journal      + " varchar(" + journalMax  + ") not null, " + 
	    month        + " varchar(" + monthMax  + ") not null, " + 
	    number       + " varchar(" + numberMax  + ") not null, " + 
	    pages        + " varchar(" + pagesMax  + ") not null, " + 
	    publisher    + " varchar(" + publisherMax  + ") not null, " + 
	    school       + " varchar(" + schoolMax  + ") not null, " + 
	    volume       + " varchar(" + volumeMax  + ") not null, " + 
	    year         + " varchar(" + yearMax  + ") not null, " + 
	    bibTexKey    + " varchar(" + bibTexKeyMax  + ") not null, " + 
	    pdf          + " varchar(" + pdfMax  + ") not null, " + 
	    active       + " int default 1,"+
	    url          + " varchar(" + urlMax  + ") not null);" ;
 
    public static final String createTableCitations = "CREATE TABLE CITATIONS (" +
	    paper        + " varchar(" + paperMax  + ") not null, " + 
	    cites        + " varchar(" + citesMax  + ") not null, " +
	    probability  + " float, "+
	    outOf        + " int, "+
	    title        + " varchar(" + titleMax  + ") not null, " + 
	    authors      + " varchar(" + authorsMax  + ") not null, " + 
	    active       + " int default 1,"+
	    howpublished + " varchar(" + howpublishedMax  + ") not null);"; 

    public static final String createTableFlatTitles = "CREATE TABLE FLATTITLES (" +
	    flattitle    + " varchar(" + flattitleMax+") not null primary key, "+
	    active       + " int default 1,"+
	    paperKey     + " varchar(" + paperKeyMax +") not null);";

    public static final String createTablePDFFiles = "CREATE TABLE PDFFILES (" +
	    md5          + " varchar(" + md5Max+") not null primary key, "+
	    analysed     + " varchar(" + analysedMax+") not null, "+
	    copyrighted  + " varchar(" + copyrightedMax+") not null, "+
	    date         + " varchar(" + dateMax+") not null, "+
	    data         + " text not null, "+
	    howpublished + " varchar(" + howpublishedMax  + ") not null, " + 
	    title        + " varchar(" + titleMax  + ") not null, " + 
	    authors      + " varchar(" + authorsMax  + ") not null, " + 
	    email        + " varchar(" + emailMax  + ") not null, " + 
	    active       + " int default 1,"+
	    paperKey     + " varchar(" + paperKeyMax +") not null);";


    public static final String createTableEmails = "CREATE TABLE EMAILS (" +
	    email        + " varchar(" + emailMax+") not null primary key, "+
	    userID       + " varchar(" + userIDMax+") not null);";


    public static final String createTableUsers = "CREATE TABLE USERS (" +
	    userID       + " varchar(" + userIDMax+") not null primary key, "+
	    password     + " varchar(" + passwordMax+") not null, "+
	    cookie       + " varchar(" + cookieMax+") not null, "+
	    resetPassword+ " varchar(" + resetPasswordMax+") not null, "+
	    givenName      + " varchar(" + givenNameMax+") not null, "+
	    surname      + " varchar(" + surnameMax + ") not null, "+
	    affiliation  + " varchar(" + affiliationMax +") not null);";

    public static final String createTableUserPapers = "CREATE TABLE USERPAPERS (" +
	    paperKey     + " varchar(" + paperKeyMax+") not null, "+
	    md5          + " varchar(" + md5Max+") not null, "+
	    userID       + " varchar(" + userIDMax+") not null," +
	    sequence     + " serial," +
	    personalNotes+ " text not null);";

    public static final String createTableFileQueue = "CREATE TABLE FILEQUEUE (" +
	    sequence     + " serial primary key," +
	    type         + " varchar(" + typeMax+") not null, "+
	    paperKey     + " varchar(" + paperKeyMax+")," +
	    md5          + " varchar(" + md5Max+"), "+
	    userID       + " varchar(" + userIDMax+")," +
	    originalFile + " varchar(" + originalFileMax+"), "+
	    url          + " varchar(" + urlMax+"), "+
	    hopCount     + " int default 0, "+
	    fileName     + " varchar(" + fileNameMax+"), "+
	    fileSize     + " int, "+
	    copyrighted  + " varchar(" + copyrightedMax+"));";

    public static final String createTableAuthors = "CREATE TABLE AUTHORS (" +
	    paperKey     + " varchar(" + paperKeyMax +") not null, " +
	    authors      + " varchar(" + authorsMax  + ") not null, " + 
	    active       + " int default 1,"+
        sequence     + " int);";

    public static final String createTableUrls = "CREATE TABLE URLS (" +
	    url          + " varchar(" + urlMax + ") primary key not null, " +
	    status       + " varchar(" + statusMax  + ") not null, " + 
	    date         + " timestamp default LOCALTIMESTAMP);";

    public static final String createTableTags="CREATE TABLE TAGS (" +
		tag			+  " varchar(" +maxTagLength+") not null, " + 
		tagid			+  " serial primary key, " + 	// bigint or bigserial?? bigserial autoincrements
	    paperKey     + " varchar(" + paperKeyMax + ") not null, " +
	    userID       + " varchar(" + userIDMax + ") not null, " + 
	    isinvisible + "  boolean default false, " +
	    tagorigin + " char check ("+tagorigin+" in ('a','d')) default 'd', "+
	    isprivate + "  boolean default false, " +
	    date         + " timestamp default LOCALTIMESTAMP);";

	public static final String createTableTagLinks="CREATE TABLE TAGLINKS ("+
		" confidence int not null default 100, "+
		" subjecttag bigint not null, "+
		" objecttag bigint not null, "+
		" relation varchar(8) not null, "+
		date +" timestamp default LOCALTIMESTAMP);";
	
    public static String q(String x, int max) {
        x = x.replaceAll("'","''").replaceAll("\\\\","\\\\\\\\");
        if (x.length() > max) {
            x = x.substring(0,max);
            x = x.replaceAll("[\\\\']*$","");
        }
        return x;
    }

    static Random r = new Random();
    
    public static String randomCookie() {
        String x = Long.toHexString(r.nextLong());
        while (x.length() < 16) {
            x = "0" + x;
        }
        return x;
    }

    public static String today() {
        return df.format(new Date());
    }

    private static void createTables() {
        Statement st = null;
        String query = null;
        try {
            st = DB.connection.createStatement();
            st.executeUpdate(createTableTags);
            st.executeUpdate(createTableTagLinks);
            st.executeUpdate(createTableUrls);
            st.executeUpdate(createTableFileQueue);
            st.executeUpdate(createTableAuthors);
            st.executeUpdate(createTableUserPapers);
            st.executeUpdate(createTableUsers);
            st.executeUpdate(createTableEmails);
            st.executeUpdate(createTablePapers);
            st.executeUpdate(createTableCitations);
            st.executeUpdate(createTableFlatTitles);
            st.executeUpdate(createTablePDFFiles);
        } catch (SQLException e) {
            System.err.println("Failed to create tables: " + e);
        } finally {
            try {
                if (st != null) st.close();
            } catch(Exception e) {
                System.err.println("Fuck..." + e);
            }
        }
    }

    private static void dropTables() {
        Statement st = null;
        String query = null;
        try {
            st = DB.connection.createStatement();
            st.executeUpdate("DROP TABLE urls");
            st.executeUpdate("DROP TABLE filequeue");
            st.executeUpdate("DROP TABLE authors");
            st.executeUpdate("DROP TABLE userpapers");
            st.executeUpdate("DROP TABLE users");
            st.executeUpdate("DROP TABLE emails");
            st.executeUpdate("DROP TABLE papers");
            st.executeUpdate("DROP TABLE citations");
            st.executeUpdate("DROP TABLE flattitles");
            st.executeUpdate("DROP TABLE pdffiles");
            st.executeUpdate("DROP TABLE tags");
            st.executeUpdate("DROP TABLE taglinks");
        } catch (SQLException e) {
            System.err.println("Failed to drop tables: " + e);
        } finally {
            try {
                if (st != null) st.close();
            } catch(Exception e) {
                System.err.println("Fuck..." + e);
            }
        }
    }

    public static void main(String[] args) {
        String magic = "" + (System.currentTimeMillis()/20000 % 9000 + 1000);
        // This gives you 20 seconds to type the magic command.
        if (args.length > 1 && args[0].equals("new") && args[1].equals(magic)) {
            dropTables();
            createTables();
        } else if (args.length > 0 && args[0].equals("create")) {
            createTables();
        } else {
            System.err.println("Usage: prog [create | new " + magic + "]");
        }
    }

}







